package com.rd.project.service;

import com.rd.project.model.Klas;
import com.rd.project.model.Score;
import com.rd.project.model.Student;
import com.rd.project.model.StudentKlas;
import com.rd.project.model.Test;
import com.rd.project.model.Vak;
import java.io.FileInputStream;
import java.util.Iterator;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

@Stateless
public class UploadServiceImpl implements UploadService {

    @PersistenceContext
    private EntityManager em;

    @Override
    public Klas getKlasFromNaam(String naam) {
        Query q = em.createNamedQuery("Klas.getKlasFromNaam");
        q.setParameter("naam", naam);
        if (q.getResultList().size() > 0) {
            return (Klas) q.getSingleResult();
        } else {
            return null;
        }

    }

    @Override
    public Vak getVakFromNaamEnKlasId(String naam, int klasId) {
        Query q = em.createNamedQuery("Vak.getVakFromNaamEnKlasId");
        q.setParameter("naam", naam);
        q.setParameter("id", klasId);
        if (q.getResultList().size() > 0) {
            return (Vak) q.getSingleResult();
        } else {
            return null;
        }
    }

    @Override
    public Test getTestFromNaamEnVakId(String naam, int vakId) {
        Query q = em.createNamedQuery("Test.getTestFromNaamEnVakId");
        q.setParameter("naam", naam);
        q.setParameter("id", vakId);
        if (q.getResultList().size() > 0) {
            return (Test) q.getSingleResult();
        } else {
            return null;
        }
    }

    @Override
    public Student getStudentFromNummer(String nummer) {
        Query q = em.createNamedQuery("Student.getStudentFromNummer");
        q.setParameter("studentnr", nummer);
        if (q.getResultList().size() > 0) {
            return (Student) q.getSingleResult();
        } else {
            return null;
        }
    }

    @Override
    public void verwerkExcel(String path) {

        FileInputStream file = null;
        OPCPackage pkg = null;

        try {
            //File ophalen
            file = new FileInputStream(path);
            //File openen
            pkg = OPCPackage.open(file);

            //Excel ophalen
            XSSFWorkbook workbook = new XSSFWorkbook(pkg);

            //Sheet openen
            XSSFSheet sheet = workbook.getSheetAt(0);

            //Elke rij doorlopen
            Iterator<Row> rowIterator = sheet.iterator();

            //Gegevens van test ophalen
            String klasNaam = sheet.getRow(0).getCell(1).toString();
            String vakNaam = sheet.getRow(1).getCell(1).toString();
            String testNaam = sheet.getRow(2).getCell(1).toString();

            Klas klas = this.getKlasFromNaam(klasNaam);

            if (klas == null) {
                klas = new Klas();
                klas.setNaam(klasNaam);

                em.persist(klas);
            }

            Vak vak = this.getVakFromNaamEnKlasId(vakNaam, klas.getId().intValue());

            if (vak == null) {
                vak = new Vak();
                vak.setKlasId(klas.getId().intValue());
                vak.setNaam(vakNaam);

                em.persist(vak);
            }

            Test test = this.getTestFromNaamEnVakId(testNaam, vak.getId().intValue());

            if (test == null) {
                test = new Test();
                test.setNaam(testNaam);
                test.setTotaal((int) Double.parseDouble(sheet.getRow(3).getCell(1).toString()));
                test.setVakId(vak.getId());

                em.persist(test);
            }

            while (rowIterator.hasNext()) {
                //Alle rijen doorlopen
                Row row = rowIterator.next();
                if (row.getRowNum() <= 5) {
                    continue;
                }

                String studentNummer = row.getCell(0).toString();
                String studentNaam = row.getCell(1).toString();
                int score = (int) row.getCell(2).getNumericCellValue();

                Student student = this.getStudentFromNummer(studentNummer);
                if (student == null) {
                    student = new Student();
                    student.setNaam(studentNaam);
                    student.setStudentNr(studentNummer);

                    em.persist(student);
                    
                    StudentKlas studentKlas = new StudentKlas();
                    studentKlas.setKlasId(klas.getId());
                    studentKlas.setStudentId(student.getId());
                    
                    em.persist(studentKlas);
                }
                
                Score resultaat = new Score();
                resultaat.setScore(score);
                resultaat.setStudentId(student.getId());
                resultaat.setTestId(test.getId());
                
                em.persist(resultaat);
            }
            file.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}
